import mysql from 'mysql2/promise';

// 数据库配置接口
interface DatabaseConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  charset: string;
  timezone: string;
  acquireTimeout: number;
  timeout: number;
  reconnect: boolean;
  connectionLimit: number;
  queueLimit: number;
}

// 默认数据库配置
const defaultConfig: DatabaseConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'root',
  database: process.env.DB_NAME || 'm3u8_crawler',
  charset: 'utf8mb4',
  timezone: '+08:00',
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true,
  connectionLimit: 10,
  queueLimit: 0,
};

// 创建连接池
let pool: mysql.Pool | null = null;

/**
 * 获取数据库连接池
 */
export function getPool(): mysql.Pool {
  if (!pool) {
    pool = mysql.createPool(defaultConfig);
    
    // 监听连接池事件
    pool.on('connection', (connection) => {
      console.log('数据库连接已建立:', connection.threadId);
    });
    
    // 注意：mysql2 的 Pool 对象没有 'error' 事件，这里移除错误监听
  }
  
  return pool;
}

/**
 * 执行查询语句
 * @param sql SQL查询语句
 * @param params 查询参数
 * @returns 查询结果
 */
export async function query<T = unknown>(sql: string, params: unknown[] = []): Promise<T[]> {
  const connection = getPool();
  try {
    const [rows] = await connection.execute(sql, params);
    return rows as T[];
  } catch (error) {
    console.error('数据库查询错误:', error);
    throw error;
  }
}

/**
 * 执行单条查询
 * @param sql SQL查询语句
 * @param params 查询参数
 * @returns 查询结果的第一条记录
 */
export async function queryOne<T = unknown>(sql: string, params: unknown[] = []): Promise<T | null> {
  const results = await query<T>(sql, params);
  return results.length > 0 ? results[0] : null;
}

/**
 * 执行插入操作
 * @param sql SQL插入语句
 * @param params 插入参数
 * @returns 插入结果
 */
export async function insert(sql: string, params: unknown[] = []): Promise<mysql.ResultSetHeader> {
  const connection = getPool();
  
  try {
    const [result] = await connection.execute(sql, params);
    return result as mysql.ResultSetHeader;
  } catch (error) {
    console.error('数据库插入错误:', error);
    throw error;
  }
}

/**
 * 执行更新操作
 * @param sql SQL更新语句
 * @param params 更新参数
 * @returns 更新结果
 */
export async function update(sql: string, params: unknown[] = []): Promise<mysql.ResultSetHeader> {
  const connection = getPool();
  
  try {
    const [result] = await connection.execute(sql, params);
    return result as mysql.ResultSetHeader;
  } catch (error) {
    console.error('数据库更新错误:', error);
    throw error;
  }
}

/**
 * 执行删除操作
 * @param sql SQL删除语句
 * @param params 删除参数
 * @returns 删除结果
 */
export async function remove(sql: string, params: unknown[] = []): Promise<mysql.ResultSetHeader> {
  const connection = getPool();
  
  try {
    const [result] = await connection.execute(sql, params);
    return result as mysql.ResultSetHeader;
  } catch (error) {
    console.error('数据库删除错误:', error);
    throw error;
  }
}

/**
 * 开始事务
 * @returns 事务连接
 */
export async function beginTransaction(): Promise<mysql.PoolConnection> {
  const connection = getPool();
  const conn = await connection.getConnection();
  await conn.beginTransaction();
  return conn;
}

/**
 * 提交事务
 * @param conn 事务连接
 */
export async function commit(conn: mysql.PoolConnection): Promise<void> {
  try {
    await conn.commit();
  } catch (error) {
    console.error('事务提交错误:', error);
    throw error;
  } finally {
    conn.release();
  }
}

/**
 * 回滚事务
 * @param conn 事务连接
 */
export async function rollback(conn: mysql.PoolConnection): Promise<void> {
  try {
    await conn.rollback();
  } catch (error) {
    console.error('事务回滚错误:', error);
    throw error;
  } finally {
    conn.release();
  }
}

/**
 * 测试数据库连接
 * @returns 连接是否成功
 */
export async function testConnection(): Promise<boolean> {
  try {
    const result = await queryOne<{ test: number }>('SELECT 1 as test');
    return result?.test === 1;
  } catch (error) {
    console.error('数据库连接测试失败:', error);
    return false;
  }
}

/**
 * 关闭数据库连接池
 */
export async function closePool(): Promise<void> {
  if (pool) {
    await pool.end();
    pool = null;
    console.log('数据库连接池已关闭');
  }
}

/**
 * 获取连接池状态
 */
export function getPoolStatus() {
  if (!pool) {
    return { status: 'not_initialized' };
  }
  
  return {
    status: 'active',
    totalConnections: pool.pool.config.connectionLimit,
    // 注意：mysql2 的 pool 对象没有直接的状态属性，这里返回基本信息
  };
}

// 导出配置（用于调试）
export { defaultConfig as dbConfig };
